<?php
/**
 * Created by PhpStorm.
 * User: Yieong
 * Date: 14/10/20
 * Time: 下午5:44
 */

class ModMarket
{

    public static function levelData($params)
    {
        $table = 'tblGameScriberLevel';
        $where = ' WHERE RecDate = :date AND Platform= :platform AND GameSrc=:game and Country = "ALL" and RoleLevel != 9999 ';
        $args = array(
            ':date'     => $params['date'],
            ':platform' => $params['p'],
            ':game'     => $params['g'],
            ':kpi'      => $params['kpi'],
        );

        if (!empty($params['server']) && $params['server'] !== 'ALL' && $params['server'] !== 'LIST') {
            $where .= 'AND SID=:server';
            $args[':server'] = $params['server'];
        } else if ($params['server'] == 'LIST') {
            $where .= 'AND SID in (:server)';
            $args[':server'] = PubBase::getAllServer($params['p'], $params['g']);
        } else {
            $where .= 'AND SID = "ALL"';
        }

        $sql = "SELECT RecDate,SID,RoleLevel,LiveUser,NoRegButLiveUser
                FROM {$table} {$where} order by RecDate desc, SID asc";

        $dbLink = new PubDbLink('dbSelf', PubDatabase::getMgmtDbName($params['p'], $params['g']));

        return $dbLink->query($sql, $args);
    }


    public static function detailData($params,$count = false)
    {
        if(!array_key_exists($params['kpi'],self::$querys)){
            return array();
        }

        $sql = self::getQuerySql($params['g'],$params['kpi'],$count);
        if(!$count){

            if($params['order']){
                $order = " order by " . implode(" ",$params['order']);
            }

            if(strpos(strtolower($order),"insert") !== false || strpos(strtolower($order),"select") !== false){
                $order = "";
            }
            $sql .= $order;
            $page = intval($params['page']) ? intval($params['page']) : 1;
            $length = intval($params['length']);
            $offset = ($page - 1) * $length;
            $limit = " limit $offset , $length";
            $sql .= $limit;
        }
        $dbLink = new PubDbLink('dbSelf', PubDatabase::getMgmtDbName($params['p'], $params['g']));
        return $dbLink->query($sql);
    }

    public static $querys = array(

        "SubRegRole" => "select a.useremail,a.userid,from_unixtime(a.reg_time) as regtime,ip,a.fromurl,from_unixtime(a.createtime) as createtime ,from_unixtime(last_login_time) as lastlogintime,a.country,a.fromdetail,a.reg_fromurl,c.level,c.username
        from en_data.tbllog_subscriber a left  join
        (select username, roleid, max(level) as level,max(last_login_time) as last_login_time from r2games_#game#.tbllog_player where RecDate='#date#' group by username) c
        on (a.userid=c.username) where a.userid>0 and a.gameid=198 and c.level>0 ",
        "SubRegNoRole"=>"select a.useremail,a.userid,from_unixtime(a.reg_time) as regtime,ip,a.fromurl,from_unixtime(a.createtime) as createtime ,
a.country,a.fromdetail,a.reg_fromurl,b.level,b.username
from en_data.tbllog_subscriber a left  join (select * from r2games_#game#.tbllog_player where RecDate='#date#' ) b on (a.userid=b.username)
 where a.userid>0 and a.gameid= #gameid# and b.level is null ",
        "SubNoReg"=>"select a.useremail,a.userid,from_unixtime(a.reg_time) as regtime,ip,a.fromurl,from_unixtime(a.createtime) as createtime ,
a.country,a.fromdetail,a.reg_fromurl
from en_data.tbllog_subscriber2 a
 where a.userid=0 and a.gameid= #gameid# ",
        "NoSubRegRole"=>"select a.*,from_unixtime(d.last_login_time) as lastlogintime,d.level from (
  SELECT m.*,  u.fromdetail,c.reg_time,c.useremail as regusermail FROM en_data.web_user_info AS m LEFT JOIN en_data.web_usr_source_info  AS u ON m.userid = u.usrid
  left outer join en_data.tbllog_subscriber c on (m.usermail=c.useremail and c.gameid= #gameid#)
  where sid in (#servers#))  a left  join
 (select username, roleid, max(level) as level,max(last_login_time) as last_login_time from r2games_#game#.tbllog_player where RecDate='#date#' group by username) d
 on (a.userid=d.username)
  where regusermail is null and d.level>0 ",
        "NoSubRegNoRole"=>"select a.*,from_unixtime(c.last_login_time) as lastlogintime,c.level from (
  SELECT m.*,  u.fromdetail,c.reg_time,c.useremail as regusermail FROM en_data.web_user_info AS m LEFT JOIN en_data.web_usr_source_info  AS u ON m.userid = u.usrid
  left outer join en_data.tbllog_subscriber c on (m.usermail=c.useremail and c.gameid= #gameid#)
  where sid in (#servers#))  a left  join (select * from r2games_#game#.tbllog_player where RecDate='#date#' ) c
 on (a.userid=c.username)
  where regusermail is null and c.level is null "
    );

    public static $countQuerys = array(

//        "SubRegRole" => "select count(1) as num
//        from en_data.tbllog_subscriber a left  join
//        (select username, roleid, max(level) as level,max(last_login_time) as last_login_time from r2games_#game#.tbllog_player where RecDate='#date#' group by username) c
//        on (a.userid=c.username) where a.userid>0 and a.gameid=198 and c.level>0 ",
//        "SubRegNoRole"=>"select count(1) as num
//from en_data.tbllog_subscriber a left  join (select * from r2games_#game#.tbllog_player where RecDate='#date#' ) b on (a.userid=b.username)
// where a.userid>0 and a.gameid= #gameid# and b.level is null ",
//        "SubNoReg"=>"select count(1) as num from en_data.tbllog_subscriber2 a
// where a.userid=0 and a.gameid= #gameid# ",
//        "NoSubRegRole"=>"select  count(1) as num
//from (
//  SELECT m.*,  u.fromdetail,c.reg_time,c.useremail as regusermail FROM en_data.web_user_info AS m LEFT JOIN en_data.web_usr_source_info  AS u ON m.userid = u.usrid
//  left outer join en_data.tbllog_subscriber c on (m.usermail=c.useremail and c.gameid= #gameid#)
//  where sid in (#servers#))  a left  join
// (select username, roleid, max(level) as level,max(last_login_time) as last_login_time from r2games_#game#.tbllog_player where RecDate='#date#' group by username) d
// on (a.userid=d.username)
//  where regusermail is null and d.level>0 ",
//        "NoSubRegNoRole"=>"select count(1) as num from (
//  SELECT m.*,  u.fromdetail,c.reg_time,c.useremail as regusermail FROM en_data.web_user_info AS m LEFT JOIN en_data.web_usr_source_info  AS u ON m.userid = u.usrid
//  left outer join en_data.tbllog_subscriber c on (m.usermail=c.useremail and c.gameid= #gameid#)
//  where sid in (#servers#))  a left  join (select * from r2games_#game#.tbllog_player where RecDate='#date#' ) c
// on (a.userid=c.username)
//  where regusermail is null and c.level is null "
    );

    public static function getQuerySql($game,$kpi,$count = false){
        $g = PubPlatform::findGameByName($game);
        $date = $g['marketEndDate'];
        $servers = $g['marketServers'];
        $gameid = $g['id'];
        $args = array(
            'date'  => $date,
            'game'  => $game,
            'servers'   => empty($servers) ? "99999" : implode(",",$servers),
            'gameid'    => $gameid,
            'kpi'   => $kpi,
            'table' =>  'R2MgmtDB_' . strtoupper($game),
        );

        $sql = $count ? "select count(1) as num from tblGameSubscriber where recDate='#date#' and kpi = '#kpi#'" : "select * from tblGameSubscriber where recDate='#date#' and kpi = '#kpi#'";
//        $sql = $count ? self::$countQuerys[$kpi] : self::$querys[$kpi];
        return PubFunc::parseString($sql,$args);
    }
}